<?php
/**
 * Class that operate on table 'classifieds'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2015-05-26 14:36
 */
class ClassifiedsMySqlDAO implements ClassifiedsDAO{

	/**
	 * Get Domain object by primry key
	 *
	 * @param String $id primary key
	 * @return ClassifiedsMySql 
	 */
	public function load($id){
		$sql = 'SELECT * FROM classifieds WHERE classified_id = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($id);
		return $this->getRow($sqlQuery);
	}

	/**
	 * Get all records from table
	 */
	public function queryAll(){
		$sql = 'SELECT * FROM classifieds';
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}
	
	/**
	 * Get all records from table ordered by field
	 *
	 * @param $orderColumn column name
	 */
	public function queryAllOrderBy($orderColumn){
		$sql = 'SELECT * FROM classifieds ORDER BY '.$orderColumn;
		$sqlQuery = new SqlQuery($sql);
		return $this->getList($sqlQuery);
	}
	
	/**
 	 * Delete record from table
 	 * @param classified primary key
 	 */
	public function delete($classified_id){
		$sql = 'DELETE FROM classifieds WHERE classified_id = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($classified_id);
		return $this->executeUpdate($sqlQuery);
	}
	
	/**
 	 * Insert record to table
 	 *
 	 * @param ClassifiedsMySql classified
 	 */
	public function insert($classified){
		$sql = 'INSERT INTO classifieds (category_id, subcategory_id, start_date, description, contact_details, phone_number, mobile_number, email, user_name, classified_type, show_my_contact, expire_in, classified_image, classified_url, meta_description, meta_keywords, status, created_date, updated_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';
		$sqlQuery = new SqlQuery($sql);
		
		$sqlQuery->setNumber($classified->categoryId);
		$sqlQuery->setNumber($classified->subcategoryId);
		$sqlQuery->set($classified->startDate);
		$sqlQuery->set($classified->description);
		$sqlQuery->set($classified->contactDetails);
		$sqlQuery->set($classified->phoneNumber);
		$sqlQuery->set($classified->mobileNumber);
		$sqlQuery->set($classified->email);
		$sqlQuery->set($classified->userName);
		$sqlQuery->set($classified->classifiedType);
		$sqlQuery->setNumber($classified->showMyContact);
		$sqlQuery->set($classified->expireIn);
		$sqlQuery->set($classified->classifiedImage);
		$sqlQuery->set($classified->classifiedUrl);
		$sqlQuery->set($classified->metaDescription);
		$sqlQuery->set($classified->metaKeywords);
		$sqlQuery->setNumber($classified->status);
		$sqlQuery->set($classified->created_date);
		$sqlQuery->set($classified->updated_date);

		$id = $this->executeInsert($sqlQuery);	
		$classified->classifiedId = $id;
		return $id;
	}
	
	/**
 	 * Update record in table
 	 *
 	 * @param ClassifiedsMySql classified
 	 */
	public function update($classified){
		$sql = 'UPDATE classifieds SET category_id = ?, subcategory_id = ?, start_date = ?, description = ?, contact_details = ?, phone_number = ?, mobile_number = ?, email = ?, user_name = ?, classified_type = ?, show_my_contact = ?, expire_in = ?, classified_image = ?, classified_url = ?, meta_description = ?, meta_keywords = ?, status = ?, created_date = ?, updated_date = ? WHERE classified_id = ?';
		$sqlQuery = new SqlQuery($sql);
		
		$sqlQuery->setNumber($classified->categoryId);
		$sqlQuery->setNumber($classified->subcategoryId);
		$sqlQuery->set($classified->startDate);
		$sqlQuery->set($classified->description);
		$sqlQuery->set($classified->contactDetails);
		$sqlQuery->set($classified->phoneNumber);
		$sqlQuery->set($classified->mobileNumber);
		$sqlQuery->set($classified->email);
		$sqlQuery->set($classified->userName);
		$sqlQuery->set($classified->classifiedType);
		$sqlQuery->setNumber($classified->showMyContact);
		$sqlQuery->set($classified->expireIn);
		$sqlQuery->set($classified->classifiedImage);
		$sqlQuery->set($classified->classifiedUrl);
		$sqlQuery->set($classified->metaDescription);
		$sqlQuery->set($classified->metaKeywords);
		$sqlQuery->setNumber($classified->status);
		$sqlQuery->set($classified->created_date);
		$sqlQuery->set($classified->updated_date);

		$sqlQuery->setNumber($classified->classifiedId);
		return $this->executeUpdate($sqlQuery);
	}

	/**
 	 * Delete all rows
 	 */
	public function clean(){
		$sql = 'DELETE FROM classifieds';
		$sqlQuery = new SqlQuery($sql);
		return $this->executeUpdate($sqlQuery);
	}

	public function queryByCategoryId($value){
		$sql = 'SELECT * FROM classifieds WHERE category_id = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($value);
		return $this->getList($sqlQuery);
	}

	public function queryBySubcategoryId($value){
		$sql = 'SELECT * FROM classifieds WHERE subcategory_id = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($value);
		return $this->getList($sqlQuery);
	}

	public function queryByStartDate($value){
		$sql = 'SELECT * FROM classifieds WHERE start_date = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByDescription($value){
		$sql = 'SELECT * FROM classifieds WHERE description = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByContactDetails($value){
		$sql = 'SELECT * FROM classifieds WHERE contact_details = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByPhoneNumber($value){
		$sql = 'SELECT * FROM classifieds WHERE phone_number = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByMobileNumber($value){
		$sql = 'SELECT * FROM classifieds WHERE mobile_number = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByEmail($value){
		$sql = 'SELECT * FROM classifieds WHERE email = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByUserName($value){
		$sql = 'SELECT * FROM classifieds WHERE user_name = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByClassifiedType($value){
		$sql = 'SELECT * FROM classifieds WHERE classified_type = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByShowMyContact($value){
		$sql = 'SELECT * FROM classifieds WHERE show_my_contact = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($value);
		return $this->getList($sqlQuery);
	}

	public function queryByExpireIn($value){
		$sql = 'SELECT * FROM classifieds WHERE expire_in = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByClassifiedImage($value){
		$sql = 'SELECT * FROM classifieds WHERE classified_image = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByClassifiedUrl($value){
		$sql = 'SELECT * FROM classifieds WHERE classified_url = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByMetaDescription($value){
		$sql = 'SELECT * FROM classifieds WHERE meta_description = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByMetaKeywords($value){
		$sql = 'SELECT * FROM classifieds WHERE meta_keywords = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByStatus($value){
		$sql = 'SELECT * FROM classifieds WHERE status = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($value);
		return $this->getList($sqlQuery);
	}

	public function queryByCreatedDate($value){
		$sql = 'SELECT * FROM classifieds WHERE created_date = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}

	public function queryByUpdatedDate($value){
		$sql = 'SELECT * FROM classifieds WHERE updated_date = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->getList($sqlQuery);
	}


	public function deleteByCategoryId($value){
		$sql = 'DELETE FROM classifieds WHERE category_id = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteBySubcategoryId($value){
		$sql = 'DELETE FROM classifieds WHERE subcategory_id = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByStartDate($value){
		$sql = 'DELETE FROM classifieds WHERE start_date = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByDescription($value){
		$sql = 'DELETE FROM classifieds WHERE description = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByContactDetails($value){
		$sql = 'DELETE FROM classifieds WHERE contact_details = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByPhoneNumber($value){
		$sql = 'DELETE FROM classifieds WHERE phone_number = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByMobileNumber($value){
		$sql = 'DELETE FROM classifieds WHERE mobile_number = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByEmail($value){
		$sql = 'DELETE FROM classifieds WHERE email = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByUserName($value){
		$sql = 'DELETE FROM classifieds WHERE user_name = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByClassifiedType($value){
		$sql = 'DELETE FROM classifieds WHERE classified_type = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByShowMyContact($value){
		$sql = 'DELETE FROM classifieds WHERE show_my_contact = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByExpireIn($value){
		$sql = 'DELETE FROM classifieds WHERE expire_in = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByClassifiedImage($value){
		$sql = 'DELETE FROM classifieds WHERE classified_image = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByClassifiedUrl($value){
		$sql = 'DELETE FROM classifieds WHERE classified_url = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByMetaDescription($value){
		$sql = 'DELETE FROM classifieds WHERE meta_description = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByMetaKeywords($value){
		$sql = 'DELETE FROM classifieds WHERE meta_keywords = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByStatus($value){
		$sql = 'DELETE FROM classifieds WHERE status = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->setNumber($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByCreatedDate($value){
		$sql = 'DELETE FROM classifieds WHERE created_date = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}

	public function deleteByUpdatedDate($value){
		$sql = 'DELETE FROM classifieds WHERE updated_date = ?';
		$sqlQuery = new SqlQuery($sql);
		$sqlQuery->set($value);
		return $this->executeUpdate($sqlQuery);
	}


	
	/**
	 * Read row
	 *
	 * @return ClassifiedsMySql 
	 */
	protected function readRow($row){
		$classified = new Classified();
		
		$classified->classifiedId = $row['classified_id'];
		$classified->categoryId = $row['category_id'];
		$classified->subcategoryId = $row['subcategory_id'];
		$classified->startDate = $row['start_date'];
		$classified->description = $row['description'];
		$classified->contactDetails = $row['contact_details'];
		$classified->phoneNumber = $row['phone_number'];
		$classified->mobileNumber = $row['mobile_number'];
		$classified->email = $row['email'];
		$classified->userName = $row['user_name'];
		$classified->classifiedType = $row['classified_type'];
		$classified->showMyContact = $row['show_my_contact'];
		$classified->expireIn = $row['expire_in'];
		$classified->classifiedImage = $row['classified_image'];
		$classified->classifiedUrl = $row['classified_url'];
		$classified->metaDescription = $row['meta_description'];
		$classified->metaKeywords = $row['meta_keywords'];
		$classified->status = $row['status'];
		$classified->created_date = $row['created_date'];
		$classified->updated_date = $row['updated_date'];

		return $classified;
	}
	
	protected function getList($sqlQuery){
		$tab = QueryExecutor::execute($sqlQuery);
		$ret = array();
		for($i=0;$i<count($tab);$i++){
			$ret[$i] = $this->readRow($tab[$i]);
		}
		return $ret;
	}
	
	/**
	 * Get row
	 *
	 * @return ClassifiedsMySql 
	 */
	protected function getRow($sqlQuery){
		$tab = QueryExecutor::execute($sqlQuery);
		if(count($tab)==0){
			return null;
		}
		return $this->readRow($tab[0]);		
	}
	
	/**
	 * Execute sql query
	 */
	protected function execute($sqlQuery){
		return QueryExecutor::execute($sqlQuery);
	}
	
		
	/**
	 * Execute sql query
	 */
	protected function executeUpdate($sqlQuery){
		return QueryExecutor::executeUpdate($sqlQuery);
	}

	/**
	 * Query for one row and one column
	 */
	protected function querySingleResult($sqlQuery){
		return QueryExecutor::queryForString($sqlQuery);
	}

	/**
	 * Insert row to table
	 */
	protected function executeInsert($sqlQuery){
		return QueryExecutor::executeInsert($sqlQuery);
	}
}
?>